
from openai import OpenAI
import json, re
import time

from conf import operationConfig
from common import db

llmclient = None

class DeepSeekClient:
    def __init__(self) -> None:
        self.confop = operationConfig.OperationConfig()
        self.database = db.Database.get_instance()
        self.client = OpenAI(
            api_key=self.confop.get_section_for_data("DEEPSEEK", "key"),
            base_url=self.confop.get_section_for_data("DEEPSEEK", "url")
        )
        self.model_name = self.confop.get_section_for_data("DEEPSEEK", "name")
        self.table_columns = [
            {"name": "id", "description": "识别记录标识"},
            {"name": "result", "description": "病虫害名称"},
            {"name": "confidence", "description": "识别置信度"},
            {"name": "timestamp", "description": "识别记录时间"}
        ]
        self.prompt_template = """
你是一个SQL专家，根据以下提供的表结构和需求生成对应的SQL select语句。
表结构如下：
| 列名 | 描述 |
| ---- | ---- |
{columns_info}
**要求**：{user_requirement}"""
    
    def __extract_sql_from_markdown(self, generated_sql):
        match_restul = re.search(r"```sql(.*?)```", generated_sql, re.DOTALL)
        if match_restul:
            sql = match_restul.group(1).strip()
            return sql
        else:
            return generated_sql.strip()
    
    def generate_sql(self, requirement):
        columns_info = "\n".join([f"| {col['name']} | {col['description']} |" for col in self.table_columns])
        prompt = self.prompt_template.format(columns_info=columns_info, user_requirement=requirement)

        response = self.client.chat.completions.create(
            model=self.model_name,
            messages=[
                {"role": "system", "content": "你是一个农业数据分析助手"},
                {"role": "user", "content": prompt}
            ]
        )
        
        if response.choices:
            generated_sql = response.choices[0].message.content
            sql = self.__extract_sql_from_markdown(generated_sql)
            row = db.SqlHistory(sql=sql, timestamp=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
            self.database.insert([row])
            return sql
        else:
            return "模型没有生成任何回复"

    def process_exec_result(self, result):
        prompt = "请对以下数据库查询结果进行分析和总结：" + str(result)
        response = self.client.chat.completions.create(
            model=self.model_name,
            messages=[
                {"role": "system", "content": "你是一个数据分析助手"},
                {"role": "user", "content": prompt}
            ]
        )
        return response.choices[0].message.content

    def get_suggestion_detection_result(self, result):
        prompt = f"请根据当前番茄叶片的疾病'{result}'，提出治疗建议"
        response = self.client.chat.completions.create(
            model=self.model_name,
            messages=[
                {"role": "system", "content": "你是一个番茄种植领域专家"},
                {"role": "user", "content": prompt}
            ]
        )
        return response.choices[0].message.content       

    @classmethod
    def get_instance(cls):
        global llmclient
        if llmclient is None:
            llmclient = DeepSeekClient()
        return llmclient